Release 10.1A: OpenEdge Data Management:
SQL Development

Using indexes

An index is a database object that enables quick information retrieval from a table. OpenEdge SQL employs a B-tree index, which organizes data in ascending or descending order. For every entry in the index table there is a corresponding entry in the database table to which it is associated. This speeds a query because it is more efficient to locate a row by searching a sorted index than by searching an unsorted table. This type of index is ideal for searching for a single value or a range of values.

Create an index when:

The column is commonly used in a WHERE clause or in a join condition.
The column contains a large number or a wide range of values.
Two or more columns are frequently used together in a WHERE clause or a join condition.
The table is large and most queries are expected to retrieve less than a small percentage of rows.

Do not create an index if:

The table is frequently updated.
Most queries retrieve a large percentage of the rows in a table.
The columns are not frequently used as a condition of a query.

To create or drop index information, use the CREATE INDEX and DROP INDEX statements. For information on working with indexes to optimize query performance, see Chapter 10, " Optimizing Query Performance."